﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using Mysqlx.Crud;
using Org.BouncyCastle.Asn1.X509;



public class OrderDetails
{
    public string ProductName { get; set; }
    public decimal Quantity { get; set; }
    public decimal Price { get; set; }//这是单价

    public override bool Equals(object obj)
    {
        if (obj is OrderDetails other)
        {
            return ProductName == other.ProductName && Quantity == other.Quantity && Price == other.Price;
        }
        return false;
    }

    public override int GetHashCode()
    {
        return HashCode.Combine(ProductName, Quantity, Price);
    }

    public override string ToString()
    {
        return $"Product: {ProductName}, Quantity: {Quantity}, Price: {Price}";
    }
}

public class Order
{
    public int OrderId { get; set; }
    public string Customer { get; set; }
    public List<OrderDetails> Details { get; set; } = new List<OrderDetails>();
    // 单独字段存储数据库值,原本定义无法写入
    public string _dbDetailsSummary;
    public decimal _dbTotalAmount;

    public string DetailsSummary => _dbDetailsSummary ?? (Details.Any() ?
        string.Join(", ", Details.Select(d => $"{d.ProductName}(×{d.Quantity})")) :
        "无商品");

    public decimal TotalAmount => _dbTotalAmount != 0 ? _dbTotalAmount : Details.Sum(d => d.Quantity * d.Price);

    public override bool Equals(object obj)
    {
        if (obj is Order other)
        {
            return OrderId == other.OrderId && Customer == other.Customer && Details.SequenceEqual(other.Details);
        }
        return false;
    }

    public override int GetHashCode()
    {
        return HashCode.Combine(OrderId, Customer, Details);
    }

    public override string ToString()
    {
        return $"Order ID: {OrderId}, Customer: {Customer}, Total Amount: {TotalAmount}\n\nDetails:\n{string.Join("\n", Details)}";
    }
}

public class OrderService
{
    public void AddOrder(Order order)
    {
        string connectionString = "data source=localhost;database=dingdan;user id=root;password=asdfg123456;pooling=true;charset=utf8;";
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            // 插入订单
            string orderSql = @"
                INSERT INTO orders (order_id, customer, totalAmount, detailsSummary)
                VALUES (@OrderId, @Customer, @TotalAmount, @DetailsSummary)";
            using (var orderCommand = new MySqlCommand(orderSql, connection))
            {
                orderCommand.Parameters.AddWithValue("@OrderId", order.OrderId);
                orderCommand.Parameters.AddWithValue("@Customer", order.Customer);
                orderCommand.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
                orderCommand.Parameters.AddWithValue("@DetailsSummary", order.DetailsSummary);
                orderCommand.ExecuteNonQuery();
            }

            // 插入订单详情
            string detailSql = @"
                INSERT INTO order_details (order_id, product_name, quantity, price)
                VALUES (@OrderId, @ProductName, @Quantity, @Price)";
            foreach (var detail in order.Details)
            {
                using (var detailCommand = new MySqlCommand(detailSql, connection))
                {
                    detailCommand.Parameters.AddWithValue("@OrderId", order.OrderId);
                    detailCommand.Parameters.AddWithValue("@ProductName", detail.ProductName);
                    detailCommand.Parameters.AddWithValue("@Quantity", detail.Quantity);
                    detailCommand.Parameters.AddWithValue("@Price", detail.Price);
                    detailCommand.ExecuteNonQuery();
                }
            }
        }
    }


    public void RemoveOrder(int orderId)
    {
        string connectionString = "data source=localhost;database=dingdan;user id=root;password=asdfg123456;pooling=true;charset=utf8;";
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            // 删除订单详情
            string detailSql = "DELETE FROM order_details WHERE order_id = @OrderId";
            using (var detailCommand = new MySqlCommand(detailSql, connection))
            {
                detailCommand.Parameters.AddWithValue("@OrderId", orderId);
                detailCommand.ExecuteNonQuery();
            }

            // 删除订单
            string orderSql = "DELETE FROM orders WHERE order_id = @OrderId";
            using (var orderCommand = new MySqlCommand(orderSql, connection))
            {
                orderCommand.Parameters.AddWithValue("@OrderId", orderId);
                orderCommand.ExecuteNonQuery();
            }
        }
    }

    public void UpdateOrder(Order order)
    {
        string connectionString = "data source=localhost;database=dingdan;user id=root;password=asdfg123456;pooling=true;charset=utf8;";
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            // 更新订单
            string orderSql = @"
                UPDATE orders
                SET customer = @Customer, totalAmount = @TotalAmount, detailsSummary = @DetailsSummary
                WHERE order_id = @OrderId";
            using (var orderCommand = new MySqlCommand(orderSql, connection))
            {
                orderCommand.Parameters.AddWithValue("@OrderId", order.OrderId);
                orderCommand.Parameters.AddWithValue("@Customer", order.Customer);
                orderCommand.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
                orderCommand.Parameters.AddWithValue("@DetailsSummary", order.DetailsSummary);
                orderCommand.ExecuteNonQuery();
            }

            // 删除旧的订单详情
            string deleteDetailSql = "DELETE FROM order_details WHERE order_id = @OrderId";
            using (var deleteDetailCommand = new MySqlCommand(deleteDetailSql, connection))
            {
                deleteDetailCommand.Parameters.AddWithValue("@OrderId", order.OrderId);
                deleteDetailCommand.ExecuteNonQuery();
            }

            // 插入新的订单详情
            string insertDetailSql = @"
                INSERT INTO order_details (order_id, product_name, quantity, price)
                VALUES (@OrderId, @ProductName, @Quantity, @Price)";
            foreach (var detail in order.Details)
            {
                using (var insertDetailCommand = new MySqlCommand(insertDetailSql, connection))
                {
                    insertDetailCommand.Parameters.AddWithValue("@OrderId", order.OrderId);
                    insertDetailCommand.Parameters.AddWithValue("@ProductName", detail.ProductName);
                    insertDetailCommand.Parameters.AddWithValue("@Quantity", detail.Quantity);
                    insertDetailCommand.Parameters.AddWithValue("@Price", detail.Price);
                    insertDetailCommand.ExecuteNonQuery();
                }
            }
        }
    }

    public List<Order> QueryOrders(OrderQueryParameters parameters)
    {
        var orders = new List<Order>();
        string constring = "data source=localhost;database=dingdan;user id=root;password=asdfg123456;pooling=true;charset=utf8;";
        using (var connection = new MySqlConnection(constring))
        {
            connection.Open();

            // 构建 SQL 查询
            var sql = new StringBuilder(@"
            SELECT o.order_id, o.customer, o.totalAmount, o.detailsSummary,
                   d.product_name, d.quantity, d.price
            FROM orders o
            LEFT JOIN order_details d ON o.order_id = d.order_id
            WHERE 1=1");

            var command = new MySqlCommand(sql.ToString(), connection);

            if (parameters.OrderId.HasValue)
            {
                sql.Append(" AND o.order_id = @OrderId");
                command.Parameters.AddWithValue("@OrderId", parameters.OrderId.Value);
            }

            if (!string.IsNullOrEmpty(parameters.Customer))
            {
                sql.Append(" AND o.customer LIKE @Customer");
                command.Parameters.AddWithValue("@Customer", $"%{parameters.Customer}%");
            }

            if (parameters.MinAmount.HasValue)
            {
                sql.Append(" AND o.totalamount >= @MinAmount");
                command.Parameters.AddWithValue("@MinAmount", parameters.MinAmount.Value);
            }

            if (parameters.MaxAmount.HasValue)
            {
                sql.Append(" AND o.totalamount <= @MaxAmount");
                command.Parameters.AddWithValue("@MaxAmount", parameters.MaxAmount.Value);
            }

            command.CommandText = sql.ToString();

            // 执行查询
            using (var reader = command.ExecuteReader())
            {
                var orderDict = new Dictionary<int, Order>();

                while (reader.Read())
                {
                    int orderId = reader.GetInt32("order_id");

                    if (!orderDict.ContainsKey(orderId))
                    {
                        var order = new Order
                        {
                            OrderId = orderId,
                            Customer = reader.GetString("customer"),
                        };
                        orderDict[orderId] = order;
                        orders.Add(order);
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("product_name")))
                    {
                        var detail = new OrderDetails
                        {
                            ProductName = reader.GetString("product_name"),
                            Quantity = reader.GetInt32("quantity"),
                            Price = reader.GetDecimal("price")
                        };
                        orderDict[orderId].Details.Add(detail);
                    }
                }
            }
        }

        return orders;
    }
    public class OrderQueryParameters
    {
        public int? OrderId { get; set; }
        public string Customer { get; set; }
        public decimal? MinAmount { get; set; }
        public decimal? MaxAmount { get; set; }

        public override string ToString()
        {
            var conditions = new List<string>();

            if (OrderId.HasValue)
                conditions.Add($"订单ID: {OrderId}");

            if (!string.IsNullOrEmpty(Customer))
                conditions.Add($"客户名称: {Customer}");

            if (MinAmount.HasValue || MaxAmount.HasValue)
            {
                var range = MinAmount.HasValue ? $"≥{MinAmount}" : "";
                range += MaxAmount.HasValue ? $"≤{MaxAmount}" : "";
                conditions.Add($"金额范围: {range}");
            }

            return conditions.Any()
                ? "当前搜索条件:\n" + string.Join("\n", conditions)
                : "当前无搜索条件";
        }
    }

    public List<Order> GetAllOrders()
    {
        var orders = new List<Order>();
        string constring = "data source=localhost;database=dingdan;user id=root;password=asdfg123456;pooling=true;charset=utf8;";

        using (var connection = new MySqlConnection(constring))
        {
            connection.Open();

            // 查询订单基本信息
            string orderSql = @"
        SELECT o.order_id, o.customer, o.totalAmount, o.detailsSummary
        FROM orders o
        ORDER BY o.order_id";

            using (var orderCommand = new MySqlCommand(orderSql, connection))
            using (var orderReader = orderCommand.ExecuteReader())
            {
                while (orderReader.Read())
                {
                    var order = new Order
                    {
                        OrderId = orderReader.GetInt32("order_id"),
                        Customer = orderReader.GetString("customer"),
                        _dbTotalAmount = orderReader.GetDecimal("totalAmount"),
                        _dbDetailsSummary = orderReader.GetString("detailsSummary"),
                        Details = new List<OrderDetails>() // 初始化Details列表
                    };
                    orders.Add(order);
                }
            }

            // 如果找到了订单，再查询每个订单的明细
            if (orders.Count > 0)
            {
                // 创建逗号分隔的订单ID字符串
                string orderIds = string.Join(",", orders.Select(o => o.OrderId));

                string detailSql = @"
                SELECT order_id, product_name, quantity, price
                FROM order_details
                WHERE order_id IN (@OrderIds)
                ORDER BY order_id";

                using (var detailCommand = new MySqlCommand(detailSql, connection))
                {
                    // 添加参数并设置值
                    detailCommand.Parameters.AddWithValue("@OrderIds", orderIds);

                    using (var detailReader = detailCommand.ExecuteReader())
                    {
                        var orderDict = orders.ToDictionary(o => o.OrderId);

                        while (detailReader.Read())
                        {
                            int orderId = detailReader.GetInt32("order_id");
                            if (orderDict.TryGetValue(orderId, out var order))
                            {
                                order.Details.Add(new OrderDetails
                                {
                                    ProductName = detailReader.GetString("product_name"),
                                    Quantity = detailReader.GetDecimal("quantity"),
                                    Price = detailReader.GetDecimal("price")
                                });
                            }
                        }
                    }
                }
            }
        }

        return orders;
    }

    public void SortOrders(Comparison<Order> comparison = null)
    {
        var orders = GetAllOrders();
        if (comparison == null)
        {
            orders.Sort((o1, o2) => o1.OrderId.CompareTo(o2.OrderId));
        }
        else
        {
            orders.Sort(comparison);
        }
    }
}